package comits.business.tiers.dao;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.context.ManagedSessionContext;

import comits.business.tiers.metiers.*;
import comits.business.tiers.orm.hibernate.*;
import comits.business.tiers.utilitaires.Dates;


public class DepotDAOImpl implements DepotDAO{

	public List<Depot> getListDepotFrn(int numFrn){
		Session session = HibernateSession.currentSession();
		SQLQuery sql =  session.createSQLQuery(PropertiesConfig.Get("GetDepotPrim"))
		.addEntity(Depot.class);
		/*SQLQuery sql =  session.createSQLQuery("Select * from depot where NumDpt in " +
				"(select distinct pr.numDest from produit as p,parametrereseau as pr " +
				"where NumExp = ? and expediteur='Fournisseur' and destinataire='Depot'" +
				" and p.Numpdt = pr.numpdt and p.NumFournisseur = ?)").addEntity(Depot.class);*/
		sql.setInteger(0,numFrn);
		sql.setInteger(1,numFrn);
		//System.out.println(sql.toString());
		List<Depot> list = sql.list();
		ManagedSessionContext.unbind(HibernateSession.getSessionFactory());
		return list;
	}
	
	public List<Depot> getListDepots(){
		Session session = HibernateSession.currentSession();
		SQLQuery sql =  session.createSQLQuery(PropertiesConfig.Get("GetDepots"))
		.addEntity(Depot.class);
		/*SQLQuery sql =  session.createSQLQuery("Select * from depot").addEntity(Depot.class);*/
		
		//System.out.println(sql.toString());
		List<Depot> list = sql.list();
		ManagedSessionContext.unbind(HibernateSession.getSessionFactory());
		return list;
	}

	public List<Depot> getListDepotSecond(int numDepo, int numF){
		Session session = HibernateSession.currentSession();
		SQLQuery sql =  session.createSQLQuery(PropertiesConfig.Get("GetDepotSnd"))
		.addEntity(Depot.class);
		/*SQLQuery sql =  session.createSQLQuery("Select * from depot where NumDpt in " +
				"(select distinct pr.numDest from produit as p,parametrereseau as pr " +
				"where NumExp = ? and expediteur='Depot' and destinataire='Depot'" +
				" and p.Numpdt = pr.numpdt and p.NumFournisseur = ?)").addEntity(Depot.class);*/
		sql.setInteger(0,numDepo);
		sql.setInteger(1,numF);
		List<Depot> list = sql.list();
		ManagedSessionContext.unbind(HibernateSession.getSessionFactory());
		return list;
	}

	public int getBesoinDepot(int numExp, int numPdt, String exp, Date dateRec , int numF){
		Session session = HibernateSession.currentSession();
		Transaction tc = session.beginTransaction() ;
		SQLQuery sql =  session.createSQLQuery(PropertiesConfig.Get("SelectTotalCmdBtq"));
		/*SQLQuery sql =  session.createSQLQuery("select sum(Quantite) from commande,ligneCmd  " +
				"where Commande.NumCmd= ligneCmd.NumCmd and Commande.NumExp= ? " +
				"and ligneCmd.NumPdt= ? and Commande.Expediteur=? " +
				"and ligneCmd.statut = 'Encours' and " +
				"(DateStatut = CURRENT_DATE or DateRecepPrevue IS NULL)");*/
		sql.setInteger(0,numExp);
		sql.setInteger(1, numPdt);
		sql.setString(2, exp);
		BigDecimal En = (BigDecimal) sql.uniqueResult();

		Date datR = null ;
		
		SQLQuery sql2 =  HibernateSession.currentSession()
		.createSQLQuery(PropertiesConfig.Get("getCmdUpdatSM"))
		.addEntity(Commande.class);
		/*SQLQuery sql2 =  HibernateSession.currentSession().createSQLQuery("select commande.* " +
				"from commande,ligneCmd  " +
				"where Commande.NumCmd= ligneCmd.NumCmd " +
				"and Commande.NumExp= ? and ligneCmd.NumPdt= ? " +
				"and Commande.Expediteur=? and ligneCmd.statut = 'Encours'" +
				" and DateRecepPrevue IS NULL").addEntity(Commande.class);*/
		
		sql2.setInteger(0,numExp);
		sql2.setInteger(1, numPdt);
		sql2.setString(2, exp);
		List<Commande> lst = sql2.list();

		for(Commande id : lst){
			// mise a jour des dates dans le commande et ligne commande 
			ParametreReseauDAO prDao = new ParametreReseauDAOImpl();
			ParametreReseau PR = prDao.find(id.getNumDest(),numPdt,numExp,id.getDestinataire(),numF);
			
			sql2 =  HibernateSession.currentSession()
			.createSQLQuery(PropertiesConfig.Get("SeTDateCmdSM"));
			//sql2 =  HibernateSession.currentSession().createSQLQuery("UPDATE commande SET DateRecepPrevue= ?  WHERE NumCmd = ?");
			datR = calculDateReception(PR.getPeriodicite(),PR.getDelaiLivraison());
			sql2.setDate(0, datR);
			sql2.setInteger(1,id.getNumCmd());
			//System.out.println("cmd" + id.intValue());
			//System.out.println(sql2.toString());
			int result = sql2.executeUpdate();		
			if (result ==1)System.out.println("commande SM modifiee");	
		}

		sql2 =  HibernateSession.currentSession()
		.createSQLQuery(PropertiesConfig.Get("getLCmdUpdatSM"));
		/*sql2 =  HibernateSession.currentSession().createSQLQuery("select numlcmd from commande,ligneCmd  " +
				"where Commande.NumCmd= ligneCmd.NumCmd and Commande.NumExp= ? " +
				"and ligneCmd.NumPdt= ? and Commande.Expediteur=? " +
				"and ligneCmd.statut = 'Encours' and ligneCmd.DateReception IS NULL");*/
		sql2.setInteger(0,numExp);
		sql2.setInteger(1, numPdt);
		sql2.setString(2, exp);
		List<Integer> lst2 = sql2.list();

		for(Integer en : lst2){
			// mise a jour des dates dans le commande et ligne commande pour les commandes supermarches traitees
			sql2 =  HibernateSession.currentSession()
			.createSQLQuery(PropertiesConfig.Get("SeTDateLCmdSM"));
			//sql2 =  HibernateSession.currentSession().createSQLQuery("UPDATE ligneCmd SET DateReception= ?  WHERE NumlCmd = ?");
			sql2.setDate(0, datR);
			sql2.setInteger(1,en.intValue());
			//System.out.println("lcmd" + id.intValue());
			//System.out.println(sql2.toString());
			int result = sql2.executeUpdate();		
			if (result ==1) System.out.println(" ligne commande SM modifiee");	
		}
		tc.commit();		
		ManagedSessionContext.unbind(HibernateSession.getSessionFactory());
		try{
			//System.out.println("besoin " + En.intValue());
			return En.intValue();
		}catch(NullPointerException ex){
			//System.out.println("besoin " + 0);
			return 0;
		}
		
	}

	/**
	 * calcul de la date de reception
	 * @param period
	 * @param delay
	 * @param res
	 */
	private Date calculDateReception (int period,int delay){
		// declaration des variables
		Dates datJour = new Dates();
		Dates datLivraison = new Dates();
		int perioDelai = period + delay;

		//Calcul de la date de livraison prevue en fonction de la periode + le delai
		datLivraison.getDateLivraison(perioDelai);
		
		return datLivraison.getDate().getTime();
	}
}
